/*
 ----------------------------------------------------------------------------------
                           "THE BEER-WARE COPYRIGHT LICENSE"
                                     (Revision 43)
                                      -----------
    Rymek [rymek@rymek.org] wrote this file. As long as You retain this notice and
    this file has been sent directly to You by the author, You can do whatever You
    want with this stuff as long as You don't sell or rent it. If we meet some day,
    and You think this stuff is worth it, You can buy me a beer in return :-)
                                                                 __________
                                                                 Paweł Rymaszewski
  ----------------------------------------------------------------------------------
 */

package constants;

/**
 * Constants containing all database queries used in project
 *
 * @author rymek
 *         User: rymek
 *         Date: 30.06.11
 *         Time: 10:10
 */
public interface QueryConstants {

    // SQL statement used for searching for synchronous messages
    public static final String SYNC_MESSAGES_SEARCH =
            "SELECT msg_id,\n" +
                    "       time_received,\n" +
                    "       time_updated,\n" +
                    "       processing_time,\n" +
                    "       msg_status\n" +
                    "  FROM (SELECT hdr.msgname AS msg_name,\n" +
                    "               hdr.publishtimestamp AS time_received,\n" +
                    "               hdr.lastupddttm AS time_updated,\n" +
                    "               TRUNC ( (86400 * (hdr.lastupddttm - hdr.publishtimestamp)))\n" +
                    "                  AS processing_time,\n" +
                    "               hdr.guid AS msg_id,\n" +
                    "               hdr.statusstring AS msg_status\n" +
                    "          FROM sysadm.psibloghdr hdr\n" +
                    "         WHERE hdr.msgname = ? AND hdr.apmsgver = 'VERSION_1'\n" +
                    "               AND hdr.publishtimestamp BETWEEN TO_DATE (\n" +
                    "                                                   ? ,\n" +
                    "                                                   'yyyy-MM-dd HH24:MI:SS')\n" +
                    "                                            AND TO_DATE (\n" +
                    "                                                   ? ,\n" +
                    "                                                   'yyyy-MM-dd HH24:MI:SS')\n" +
                    "               AND TRUNC (\n" +
                    "                      (86400 * (hdr.lastupddttm - hdr.publishtimestamp))) >=\n" +
                    "                      ? \n" +
                    "               AND (hdr.pubnode = ? OR hdr.pubnode = 'PSFT_CR')\n" +
                    "               AND hdr.chnlname = ?)";

    // string to append if searching through archives is required
    public static final String SYNC_MESSAGES_SEARCH_ARCHIVES =
            "UNION ALL\n" +
                    "        SELECT dtarch.msgname AS msg_name,\n" +
                    "               dtarch.publishtimestamp AS time_received,\n" +
                    "               dtarch.lastupddttm AS time_updated,\n" +
                    "               TRUNC (\n" +
                    "                  (86400 * (dtarch.lastupddttm - dtarch.publishtimestamp)))\n" +
                    "                  AS processing_time,\n" +
                    "               dtarch.guid AS msg_id,\n" +
                    "               dtarch.statusstring AS msg_status\n" +
                    "          FROM sysadm.psibloghdrarch dtarch\n" +
                    "         WHERE dtarch.msgname = ? AND dtarch.apmsgver = 'VERSION_1'\n" +
                    "               AND dtarch.publishtimestamp BETWEEN TO_DATE (\n" +
                    "                                                      ? ,\n" +
                    "                                                      'yyyy-MM-dd HH24:MI:SS')\n" +
                    "                                               AND TO_DATE (\n" +
                    "                                                      ? ,\n" +
                    "                                                      'yyyy-MM-dd HH24:MI:SS')\n" +
                    "               AND TRUNC (\n" +
                    "                      (86400 * (dtarch.lastupddttm - dtarch.publishtimestamp))) >=\n" +
                    "                      ? \n" +
                    "               AND (dtarch.pubnode = ? OR dtarch.pubnode = 'PSFT_CR')\n" +
                    "               AND dtarch.chnlname = ?)";

    // SQL statement used for searching for asynchronous messages
    public static final String ASYNC_MESSAGES_SEARCH =
            "SELECT msg_id,\n" +
                    "       time_received,\n" +
                    "       time_updated,\n" +
                    "       processing_time,\n" +
                    "       msg_status\n" +
                    "  FROM (SELECT hdr.pubid AS msg_id,\n" +
                    "               hdr.publishtimestamp AS time_received,\n" +
                    "               hdr.lastupddttm AS time_updated,\n" +
                    "               TRUNC ( (86400 * (hdr.lastupddttm - hdr.publishtimestamp)))\n" +
                    "                  AS processing_time,\n" +
                    "               hdr.statusstring AS msg_status\n" +
                    "          FROM sysadm.psapmsgpubhdr hdr\n" +
                    "         WHERE hdr.msgname = ? \n" +
                    "               AND hdr.publishtimestamp BETWEEN TO_DATE (\n" +
                    "                                                   ? ,\n" +
                    "                                                   'yyyy-MM-dd HH24:MI:SS')\n" +
                    "                                            AND TO_DATE (\n" +
                    "                                                   ? ,\n" +
                    "                                                   'yyyy-MM-dd HH24:MI:SS')\n" +
                    "               AND TRUNC ( (86400 * (hdr.lastupddttm - hdr.publishtimestamp))) >= ?\n" +
                    "               AND hdr.chnlname = ? \n" +
                    "               AND (hdr.pubnode = ? OR hdr.pubnode = 'PSFT_CR'))";

    // string to append if searching through archives is required
    public static final String ASYNC_MESSAGES_SEARCH_ARCHIVES =
            "        UNION ALL\n" +
                    "        SELECT hdrarch.pubid AS msg_id,\n" +
                    "               hdrarch.publishtimestamp AS time_received,\n" +
                    "               hdrarch.lastupddttm AS time_updated,\n" +
                    "               TRUNC (\n" +
                    "                  (86400 * (hdrarch.lastupddttm - hdrarch.publishtimestamp)))\n" +
                    "                  AS processing_time,\n" +
                    "               hdrarch.statusstring AS msg_status\n" +
                    "          FROM sysadm.psapmsgarchph hdrarch\n" +
                    "         WHERE hdrarch.msgname = ? \n" +
                    "               AND hdrarch.publishtimestamp BETWEEN TO_DATE (\n" +
                    "                                                       ? ,\n" +
                    "                                                       'yyyy-MM-dd HH24:MI:SS')\n" +
                    "                                                AND TO_DATE (\n" +
                    "                                                       ? ,\n" +
                    "                                                       'yyyy-MM-dd HH24:MI:SS')\n" +
                    "               AND TRUNC ( (86400 * (hdrarch.lastupddttm - hdrarch.publishtimestamp))) >= ?\n" +
                    "               AND hdrarch.chnlname = ? \n" +
                    "               AND (hdrarch.pubnode = ? OR hdrarch.pubnode = 'PSFT_CR'))";

    // SQL statement used for fetching synchronous messages
    public static final String SYNC_MESSAGES_FETCH =
            "SELECT data_len, xml_file, msg_type\n" +
                    "  FROM (SELECT dt.guid AS msg_id,\n" +
                    "               dt.uncompdatalen AS data_len,\n" +
                    "               dt.logtype AS msg_type,\n" +
                    "               dt.pubdatalong AS xml_file\n" +
                    "          FROM sysadm.psiblogdata dt\n" +
                    "         WHERE dt.guid = ? AND (dt.logtype = ? OR dt.logtype = ? ))";

    // string to append if fetching from archives is required
    public static final String SYNC_MESSAGES_FETCH_ARCHIVES =
            "UNION ALL\n" +
                    "        SELECT dtarch.guid AS msg_id,\n" +
                    "               dtarch.uncompdatalen  AS data_len,\n" +
                    "               dtarch.logtype AS msg_type,\n" +
                    "               dtarch.pubdatalong AS xml_file\n" +
                    "          FROM sysadm.psiblogdataarch dtarch\n" +
                    "         WHERE dtarch.guid = ? AND (dtarch.logtype = ? OR dtarch.logtype = ?))";

    // SQL statement used for fetching asynchronous messages
    public static final String ASYNC_MESSAGES_FETCH =
            "SELECT data_size, xml_file\n" +
                    "  FROM (SELECT dt.pubid AS msg_id,\n" +
                    "               dt.uncompmimedatalen AS data_size,\n" +
                    "               dt.mimedatalong AS xml_file,\n" +
                    "               dt.chnlname AS msg_channel,\n" +
                    "               dt.subsegmentno AS msg_subsegment\n" +
                    "          FROM sysadm.psapmsgpubdata dt\n" +
                    "         WHERE dt.subsegmentno = 1 AND dt.pubid = ? AND dt.chnlname = ?)";

    // string to append if fetching from archives is required
    public static final String ASYNC_MESSAGES_FETCH_ARCHIVES =
            "        UNION ALL\n" +
                    "        SELECT dtarch.pubid AS msg_id,\n" +
                    "               dtarch.uncompmimedatalen AS data_size,\n" +
                    "               dtarch.mimedatalong AS xml_file,\n" +
                    "               dtarch.chnlname AS msg_channel,\n" +
                    "               dtarch.subsegmentno AS msg_subsegment\n" +
                    "          FROM sysadm.psapmsgarchpd dtarch\n" +
                    "         WHERE     dtarch.subsegmentno = 1\n" +
                    "               AND dtarch.pubid = ?\n" +
                    "               AND dtarch.chnlname = ?)";

    // SQL statement used for loading all messages active on specific database
    public static final String FETCH_AVAILABLE_MESSAGES =
            "SELECT atrx.rqstmsgname,\n" +
                    "         atrx.respmsgname,\n" +
                    "         atrx.trxtype,\n" +
                    "         atrx.effdt,\n" +
                    "         defn.chnlname,\n" +
                    "         atrx.msgnodename,\n" +
                    "         oper.method_name,\n" +
                    "         atrx.eff_status\n" +
                    "    FROM sysadm.psnodetrx atrx,\n" +
                    "         sysadm.psmsgdefn defn,\n" +
                    "         sysadm.ps_tp_wm_operations oper,\n" +
                    "         sysadm.ps_tp_msg_pub_chnl pubchnl\n" +
                    "   WHERE     atrx.rqstmsgname = defn.msgname\n" +
                    "         AND atrx.rqstmsgver = defn.defaultver\n" +
                    "         AND atrx.conngatewayid <> 'LOCAL'\n" +
                    "         AND atrx.VERSION <> 1\n" +
                    "         AND atrx.rqstmsgname = oper.input_msg_name(+)\n" +
                    "         AND atrx.rqstmsgname = pubchnl.msgname(+)\n" +
                    "         AND atrx.eff_status = 'A'\n" +
                    "         AND atrx.effdt =\n" +
                    "                (SELECT MAX (btrx.effdt)\n" +
                    "                   FROM sysadm.psnodetrx btrx\n" +
                    "                  WHERE     btrx.msgnodename = atrx.msgnodename\n" +
                    "                        AND btrx.trxtype = atrx.trxtype\n" +
                    "                        AND btrx.rqstmsgname = atrx.rqstmsgname\n" +
                    "                        AND btrx.rqstmsgver = atrx.rqstmsgver\n" +
                    "                        AND btrx.effdt <=\n" +
                    "                               TO_DATE (TO_CHAR (SYSDATE, 'YYYY-MM-DD'),\n" +
                    "                                        'YYYY-MM-DD'))";

    // SQL statement used for loading all custom publisher messages
    public static final String CPUBLISHER_FETCH_AVAILABLE_MESSAGES =
            "SELECT msg_name, server_id FROM sysadm.sp_msg_def";

    // SQL statement used for searching for custom publisher messages
    public static final String CPUBLISHER_MESSAGE_SEARCH =
            "SELECT sppub.pub_id AS msg_id,\n" +
                    "       sppub.req_date AS time_received,\n" +
                    "       sphdr.pub_date AS time_updated,\n" +
                    "       TRUNC ( (86400 * (sphdr.pub_date - sppub.req_date)))\n" +
                    "          AS processing_time\n" +
                    "  FROM sysadm.sp_msg_hdr sphdr, sysadm.sp_msg_pub sppub\n" +
                    " WHERE     sphdr.pub_id = sppub.pub_id\n" +
                    "       AND sppub.msg_name = ?\n" +
                    "       AND sppub.server_id = ?\n" +
                    "       AND sppub.pub_status = 'PUB'\n" +
                    "       AND sphdr.status = 'DONE'\n" +
                    "       AND sppub.server_id = sphdr.server_id\n" +
                    "       AND sppub.req_date BETWEEN TO_DATE (?, 'yyyy-MM-dd HH24:MI:SS')\n" +
                    "                              AND TO_DATE (?, 'yyyy-MM-dd HH24:MI:SS')";

    // SQL statement used for fetching custom publisher messages
    public static final String CPUBLISHER_MESSAGE_FETCH =
            "SELECT msg_text\n" +
                    "  FROM sysadm.sp_msg_table\n" +
                    " WHERE server_id = ? AND pub_id = ?";


    // TODO: dodac wyciaganie subscription i publication errorow z tabel:

    // TODO: psiblogerr - dla synchronow z msg katalogu
    // TODO: psiblogerrp - dla synchronow inne

    // TODO: PSAPMSGPUBERR - komunikaty bledow oparte na message catalogu, dla wychodzacych asyn.
    // TODO: PSAPMSGPUBERRP - dla asynchronow wychodzacych

    // TODO: PSAPMSGSUBCERR - dla asynchronow przychodzacych
    // TODO: PSAPMSGSUBCERRP - dla asynchronow przychodzacych

    // TODO: PSMSGCATLANG - komunikaty z message catalogu
}
